import pandas as pd
from MyTT import *
import akshare as ak
import numpy as np
import os
from datetime import datetime
from sqlalchemy import create_engine, distinct, or_, and_
import sqlite3
import backtrader as bt
import pymssql
from urllib.parse import quote_plus as urlquote
from configparser import ConfigParser
import random

pd.set_option('display.max_columns', None)
pd.set_option('display.max_rows', None)

conf = ConfigParser()
conf.read('env.ini')

sqlserver = ('sqlserver_out', 'sqlserver_in')[0]

host = conf.get(sqlserver, 'host')
port = conf.get(sqlserver, 'port')
user = conf.get(sqlserver, 'user')
password = conf.get(sqlserver, 'password')
schema = conf.get(sqlserver, 'schema')
db_name = conf.get(sqlserver, 'db')


class FactorsStrategy(bt.Strategy):
    params = (('maperiod', 20), ('money', 1e4), ('printlog', False),)

    def log(self, txt, dt=None, doprint=False):
        if self.params.printlog or doprint:
            dt = dt or self.datas[0].datetime.date(0)
            # print(f'{dt.isoformat()},{txt}')
            print(txt)

    @staticmethod
    def percent(today, yesterday):
        return float(today - yesterday) / today

    def __init__(self):
        self.order = None
        self.buy_list = []
        # self.buy_stock = get_stocks()
        # self.trade_dates = self.buy_stock['trade_date'].unique().tolist()
        self.order_list = []  # 记录以往订单，方便调仓日对未完成订单做处理
        self.buy_price = 1e4  # 每个证券买入金额，不满一手成交一手
        self.daily_pnl = dict()
        self.trade_info = list()
        self.buy_info = dict()  # 证券买入信息，包括买入时间和买入成本，例如{‘000001’: {'date': 20230614, 'cost': 14.23}}
        self.lb = dict()
        self.lb_count = dict()
        self.lb_fit = dict()
        self.period_high = dict()
        self.code_high = dict()
        self.price_high = dict()
        self.base_line = dict()  # 每个证券止损线
        self.cache_log = list()
        self.buy_price = dict()
        self.signal = dict()

        for data in self.datas:
            self.signal[data] = bt.ind.SMA(data, period=self.p.maperiod)

    def downcast(self, price):
        res = self.p.money / price // 100 * 100
        return res

    def next(self):
        if self.order:
            return

        closeDate = datetime(2023, 7, 21)

        for data in self.datas:
            code = data._name
            if code == '399300':
                continue

            cdt = data.datetime.date(0)  # 获取当前的回测时间点

            if cdt != self.datas[0].datetime.date(0):
                continue

            dt = int(cdt.strftime('%Y%m%d'))

            cur_datetime = data.lines.datetime.datetime(0)
            if dt not in self.daily_pnl:
                # item = [dt, self.broker.getvalue() - init_cash]
                self.daily_pnl[dt] = round(self.broker.getvalue() - init_cash, 2)

            pos = self.getposition(data).size

            if not pos:
                amount = self.downcast(data.close[0])
                self.order = self.buy(data=data, size=amount, name=code)
            elif cdt.year == closeDate.year and cdt.month == closeDate.month and cdt.day == closeDate.day:
                self.order = self.close(data=data, name=code)

    def notify_order(self, order):
        # 未被处理的订单
        if order.status in [order.Submitted, order.Accepted]:
            return
        # 已经处理的订单
        if order.status in [order.Completed, order.Canceled, order.Margin]:
            code = order.data._name
            if order.isbuy():
                self.log(
                    f'code={code} BUY CREATE TIME: {bt.num2date(order.created.dt)}, EXECUTED TIME: {bt.num2date(order.executed.dt)}')
                self.log(
                    'BUY EXECUTED, ref:%.0f，Price: %.2f, Cost: %.2f, Comm %.2f, Size: %.2f, Stock: %s' %
                    (order.ref,  # 订单编号
                     order.executed.price,  # 成交价
                     order.executed.value,  # 成交额
                     order.executed.comm,  # 佣金
                     order.executed.size,  # 成交量
                     code))  # 股票名称
                trade_dt = int(bt.num2date(order.created.dt).strftime('%Y%m%d%H%M%S'))
                trade_item = [trade_dt, code, 'BUY', round(order.executed.price, 2), order.executed.size,
                              round(order.executed.comm, 2)]
                self.buy_price[code] = order.executed.price
                self.trade_info.append(trade_item)
            else:  # Sell
                self.log(
                    f'code={code} SELL CREATE TIME: {bt.num2date(order.created.dt)}, EXECUTED TIME: {bt.num2date(order.executed.dt)}')
                self.log('SELL EXECUTED, ref:%.0f, Price: %.2f, Cost: %.2f, Comm %.2f, Size: %.2f, Stock: %s' %
                         (order.ref,
                          order.executed.price,
                          order.executed.value,
                          order.executed.comm,
                          order.executed.size,
                          code))
                trade_dt = int(bt.num2date(order.created.dt).strftime('%Y%m%d%H%M%S'))
                trade_item = [trade_dt, code, 'SELL', round(order.executed.price, 2),
                              order.executed.size, round(order.executed.comm, 2)]
                self.trade_info.append(trade_item)
            self.bar_executed = len(self)

        # Write down: no pending order
        self.order = None

    def notify_trade(self, trade):
        if not trade.isclosed:
            return

        self.log("OPERATION PROFIT, GROSS %.2f, NET %.2f" % (trade.pnl, trade.pnlcomm))

    def stop(self):
        print('Strategy Finish!!!')

        with open(r'C:\Users\AndrewX\Desktop\cache_log.txt', 'w') as f:
            res = '\n'.join(self.cache_log)
            f.write(res)

        daily_pnl_df = pd.DataFrame(self.daily_pnl.items(), columns=['DATE_T', 'PNL'])
        append_df = pd.DataFrame([[101, round((self.broker.getvalue() / init_cash - 1) * 100, 2)]],
                                 columns=['DATE_T', 'PNL'])
        daily_pnl_df = daily_pnl_df.append(append_df, ignore_index=True)
        trade_df = pd.DataFrame(self.trade_info, columns=['DATE_T', 'STK_CODE', 'ACTION', 'PRICE', 'AMOUNT', 'COMM'])
        pnl_table = 'TEST_DAILY_PNL'
        trade_table = 'TEST_TRADE_INFO'
        # daily_pnl_df = daily_pnl_df.loc[(daily_pnl_df['DATE_T'] >= 20230428) | (daily_pnl_df['DATE_T'] == 101)]
        insert_db(daily_pnl_df, pnl_table)
        insert_db(trade_df, trade_table)
        print('insert table ok')


def get_factors():
    cols = ['代码', '最新价', '总市值', '流通市值', '市盈率-动态', '市净率']
    df = ak.stock_sh_a_spot_em()
    ret = df[(~df['名称'].str.contains('ST')) & (~df['名称'].str.contains('N')) & (~df['名称'].str.contains('C'))][cols]
    df = ak.stock_sz_a_spot_em()
    ret2 = df[(~df['名称'].str.contains('ST')) & (~df['名称'].str.contains('N')) & (~df['名称'].str.contains('C'))][cols]
    ret = ret.append(ret2, ignore_index=True)
    ret['mvb'] = ret['总市值'] / ret['最新价']
    ret.rename(columns={'市盈率-动态': 'pe', '市净率': 'pb', '代码': 'code'}, inplace=True)

    cols = ['股票代码', '每股收益', '每股净资产', '净利润-净利润']
    ret2 = ak.stock_yjbb_em(date="20230331")[cols]
    ret2.rename(columns={'股票代码': 'code', '净利润-净利润': 'tpr', '每股收益': 'esp', '每股净资产': 'bvps'}, inplace=True)
    ret2 = ret2[ret2.tpr > 0]

    ret3 = ak.stock_hold_num_cninfo(date="20230331")[['证券代码', '本期股东人数']]
    ret3.rename(columns={'证券代码': 'code', '本期股东人数': 'holders'}, inplace=True)

    res = pd.merge(ret, ret2, on=['code'])
    factors = pd.merge(res, ret3, on=['code'])
    factors['npr'] = factors['tpr'] / factors['mvb']
    factors = factors[['code', 'pe', 'pb', 'esp', 'bvps', 'npr', 'holders']]

    return factors


def scale(factors, a1=1.0, a2=1.0, a3=1.0, a4=1.0):
    pe = -a1 * factors.pe / factors.pe.mean()
    esp = a2 * factors.esp / factors.esp.mean()
    bvps = a3 * factors.bvps / factors.bvps.mean()
    pb = a4 * factors.pb / factors.pb.mean()
    # npr = factors.npr / factors.npr.mean()
    score = pe + esp + bvps + pb
    # 排序并画图
    score = score.sort_values()
    # print(score)
    return score


def get_data(stocks, freq='day'):
    if freq == '1min':
        table_name = 'STOCK_MIN_DATA'
    elif freq == '15min':
        table_name = 'STOCK_15MIN_DATA'
    else:
        table_name = 'STOCK_DAY_DATA'

    test_code = '399300'
    # stocks.append('000877')
    stocks.append(test_code)
    stocks_str = ','.join(f"'{tok}'" for tok in stocks)
    sql = f"SELECT * FROM {table_name} WHERE code in ({stocks_str})"
    r = exec_sql(sql)
    df = pd.DataFrame(r, columns=['code', 'datetime', 'close', 'open', 'low', 'high', 'volume', 'money'])
    df['datetime'] = df['datetime'].map(time_map)
    sql2 = f"SELECT COUNT(1) FROM {table_name} WHERE code='{test_code}'"
    r2 = exec_sql(sql2)
    test_count = r2[0][0]
    res = dict()

    for code in stocks:
        sig_df = df.loc[df['code'] == code]
        # print(f'code={code}->len={len(sig_df)}->test_count={test_count}')
        if len(sig_df) == test_count or code == test_code:
            sig_df.sort_values('datetime', inplace=True, ignore_index=True)
            sig_df.set_index('datetime', inplace=True)
            res[code] = sig_df
    return res


def exec_sql(sql):
    conn = pymssql.connect(host=host, port=port, user=user, password=password, database=db_name)
    cursor = conn.cursor()
    cursor.execute(sql)
    r = cursor.fetchall()
    cursor.close()
    conn.close()
    return r


def time_map(x):
    t = datetime.strptime(str(x), '%Y%m%d%H%M')
    return t


def insert_db(df, table_name):
    yconnect = create_engine(f'mssql+pymssql://{user}:{urlquote(password)}@{host}:{port}/{db_name}?charset=utf8')
    pd.io.sql.to_sql(df, table_name, yconnect, schema=schema, if_exists='append', index=False)


pre_ratio = 0.0048
ratio = 0.1
trail_ratio = 0.035
count_info = dict()
# stocks = get_stocks()
init_cash = 1e8


def run_backtrade(stocks, index):
    cerebro = bt.Cerebro()

    data_all = get_data(stocks)
    data0 = data_all[index]
    datafeed0 = bt.feeds.PandasData(dataname=data0)
    cerebro.adddata(datafeed0, name=f'{index}')

    for code in data_all:
        if code == index:
            continue
        data = data_all[code]

        datafeed = bt.feeds.PandasData(dataname=data)
        # st_date = datetime(2023, 1, 1)
        # ed_date = datetime(2023, 6, 16)
        # datafeed = bt.feeds.PandasData(dataname=data, fromdate=st_date, todate=ed_date)
        cerebro.adddata(datafeed, name=f'{code}')
        # print(f'{code} feeds ok')

    ##   st_date = datetime.datetime(2023, 3, 1)
    ##ed_date = datetime.datetime(2023, 6, 5)

    # 初始资金 100,000,0
    cerebro.broker.setcash(init_cash)
    # 佣金，双边各 0.0003
    cerebro.broker.setcommission(commission=0.0003)
    # 滑点：双边各 0.0001
    cerebro.broker.set_slippage_perc(perc=0.0001)

    cerebro.addanalyzer(bt.analyzers.TimeReturn, _name='pnl')  # 返回收益率时序数据
    cerebro.addanalyzer(bt.analyzers.AnnualReturn, _name='_AnnualReturn')  # 年化收益率
    cerebro.addanalyzer(bt.analyzers.SharpeRatio, _name='_SharpeRatio')  # 夏普比率
    cerebro.addanalyzer(bt.analyzers.DrawDown, _name='_DrawDown')  # 回撤
    # 计算年化收益
    cerebro.addanalyzer(bt.analyzers.Returns, _name='_Returns', tann=252)

    cerebro.addstrategy(FactorsStrategy)

    result = cerebro.run()
    strat = result[0]
    # 返回日度收益率序列
    daily_return = pd.Series(strat.analyzers.pnl.get_analysis())
    # 打印评价指标
    # print("--------------- TimeReturn -----------------")
    # print(daily_return)
    # print("--------------- AnnualReturn -----------------")
    # print(strat.analyzers._AnnualReturn.get_analysis())
    # print("--------------- SharpeRatio -----------------")
    # print(strat.analyzers._SharpeRatio.get_analysis())
    # print("--------------- DrawDown -----------------")
    # print(strat.analyzers._DrawDown.get_analysis()['max'])
    # print("--------------- Return -----------------")
    # print(strat.analyzers._Returns.get_analysis())

    res = dict()
    # res['总年化收益率'] = strat.analyzers._Returns.get_analysis()['rtot']
    # res['平均年化收益率'] = strat.analyzers._Returns.get_analysis()['ravg']
    # res['复核年化收益率'] = strat.analyzers._Returns.get_analysis()['rnorm']
    res['年化收益率'] = strat.analyzers._Returns.get_analysis()['rnorm100']

    # cerebro.plot()

    return strat.analyzers._Returns.get_analysis()['rnorm100']


def main():
    index = '399300'
    times = 200
    factors = get_factors()
    random.seed()
    ret = list()

    for i in range(times):
        print(f'i={i}')
        a1 = random.randint(1, 200)
        a2 = random.randint(1, 200)
        a3 = random.randint(1, 200)
        a4 = random.randint(1, 200)
        # a1 = 10
        # a2 = 17
        # a3 = 9
        # a4 = 160
        # a1 = 167
        # a2 = 186
        # a3 = 17
        # a4 = 155
        a1 = 168
        a2 = 188
        a3 = 2
        a4 = 187
        score = scale(factors, a1, a2, a3, a4)
        codes = score[-30:].index
        stocks = list(factors[factors.index.isin(codes)].code.values)
        r = run_backtrade(stocks, index)
        item = [a1, a2, a3, a4, r]
        ret.append(item)
        break

    df = pd.DataFrame(ret, columns=['a1', 'a2', 'a3', 'a4', 'interest'])
    df.to_csv(r'C:\Users\AndrewX\Desktop\params.csv', index=0)


if __name__ == '__main__':
    main()
